<?php
if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class m_statistik_pembelian extends CI_Model {

	public function __construct() {
		parent::__construct();
	}

	function get() {
		$sql = 'SELECT KdObat, NmObat,
				(SELECT SUM(Jumlah) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat) AS item,
				(SELECT SUM(Jumlah * Harga) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat) AS transaksi
				FROM obat ORDER BY KdObat';
		return $sql;
	}

	function get_cari($tgl_awal,$tgl_akhir,$petugas,$kelompok,$obat) {
		if($petugas != ''){
			$where_petugas = "AND beli.id_user = '".$petugas."'";
		}else{
			$where_petugas = "";
		}

		if($kelompok != ''){
			$where_kelompok = "AND obat.KdKelompok = '".$kelompok."'";
		}else{
			$where_kelompok = "";
		}

		if($obat != ''){
			$where_obat = "AND detail_beli.KdObat = '".$obat."'";
		}else{
			$where_obat = "";
		}

		$sql = 'SELECT obat.KdObat, obat.NmObat,
				(SELECT SUM(Jumlah) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat
					'.$where_petugas.'
					'.$where_kelompok.'
					) AS item,
				(SELECT SUM(Jumlah * Harga) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat
					'.$where_petugas.'
					'.$where_kelompok.'
					) AS transaksi
				FROM obat
				LEFT JOIN detail_beli ON obat.KdObat = detail_beli.KdObat
				LEFT JOIN beli ON detail_beli.KdBeli = beli.KdBeli
				WHERE tanggal BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'"
				'.$where_petugas.'
				'.$where_kelompok.'
				'.$where_obat.'
				ORDER BY KdObat';
		return $sql;
	}

	function get_cari_non_tgl($petugas,$kelompok,$obat) {
		if($petugas != ''){
			$where_petugas = "AND beli.id_user = '".$petugas."'";
		}else{
			$where_petugas = "";
		}

		if($kelompok != ''){
			$where_kelompok = "AND obat.KdKelompok = '".$kelompok."'";
		}else{
			$where_kelompok = "";
		}

		if($obat != ''){
			$where_obat = "AND detail_beli.KdObat = '".$obat."'";
		}else{
			$where_obat = "";
		}

		$sql = 'SELECT obat.KdObat, obat.NmObat,
				(SELECT SUM(Jumlah) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat
					'.$where_petugas.'
					'.$where_kelompok.'
					) AS item,
				(SELECT SUM(Jumlah * Harga) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat
					'.$where_petugas.'
					'.$where_kelompok.'
					) AS transaksi
				FROM obat
				LEFT JOIN detail_beli ON obat.KdObat = detail_beli.KdObat
				LEFT JOIN beli ON detail_beli.KdBeli = beli.KdBeli
				WHERE obat.KdObat <> ""
				'.$where_petugas.'
				'.$where_kelompok.'
				'.$where_obat.'
				ORDER BY KdObat';
		return $sql;
	}

	function get_obat() {
		$sql = 'SELECT * FROM obat';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_kelompok() {
		$sql = 'SELECT * FROM kelompok';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_petugas() {
		$sql = 'SELECT rc_users.id, nickname FROM rc_users
			INNER JOIN rc_users_groups ON rc_users.id = rc_users_groups.user_id
			INNER JOIN rc_groups ON rc_users_groups.group_id = rc_groups.id
			WHERE rc_groups.name = "admin" OR rc_groups.name = "kasir"
			GROUP BY rc_users.id';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export_all() {
		$sql = 'SELECT KdObat, NmObat,
				(SELECT SUM(Jumlah) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat) AS item,
				(SELECT SUM(Jumlah * Harga) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat) AS transaksi
				FROM obat ORDER BY KdObat';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export($tgl_awal,$tgl_akhir,$petugas,$kelompok,$obat) {
		if($petugas != ''){
			$where_petugas = "AND beli.id_user = '".$petugas."'";
		}else{
			$where_petugas = "";
		}

		if($kelompok != ''){
			$where_kelompok = "AND obat.KdKelompok = '".$kelompok."'";
		}else{
			$where_kelompok = "";
		}

		if($obat != ''){
			$where_obat = "AND detail_beli.KdObat = '".$obat."'";
		}else{
			$where_obat = "";
		}

		$sql = 'SELECT obat.KdObat, obat.NmObat,
				(SELECT SUM(Jumlah) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat
					'.$where_petugas.'
					'.$where_kelompok.'
					) AS item,
				(SELECT SUM(Jumlah * Harga) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat
					'.$where_petugas.'
					'.$where_kelompok.'
					) AS transaksi
				FROM obat
				LEFT JOIN detail_beli ON obat.KdObat = detail_beli.KdObat
				LEFT JOIN beli ON detail_beli.KdBeli = beli.KdBeli
				WHERE tanggal BETWEEN "'.$tgl_awal.'" AND "'.$tgl_akhir.'"
				'.$where_petugas.'
				'.$where_kelompok.'
				'.$where_obat.'
				ORDER BY KdObat';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}

	function get_export_non_tgl($petugas,$kelompok,$obat) {
		if($petugas != ''){
			$where_petugas = "AND beli.id_user = '".$petugas."'";
		}else{
			$where_petugas = "";
		}

		if($kelompok != ''){
			$where_kelompok = "AND obat.KdKelompok = '".$kelompok."'";
		}else{
			$where_kelompok = "";
		}

		if($obat != ''){
			$where_obat = "AND detail_beli.KdObat = '".$obat."'";
		}else{
			$where_obat = "";
		}

		$sql = 'SELECT obat.KdObat, obat.NmObat,
				(SELECT SUM(Jumlah) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat
					'.$where_petugas.'
					'.$where_kelompok.'
					) AS item,
				(SELECT SUM(Jumlah * Harga) FROM beli 
					INNER JOIN detail_beli ON beli.KdBeli = detail_beli.KdBeli
					WHERE detail_beli.KdObat = obat.KdObat
					'.$where_petugas.'
					'.$where_kelompok.'
					) AS transaksi
				FROM obat
				LEFT JOIN detail_beli ON obat.KdObat = detail_beli.KdObat
				LEFT JOIN beli ON detail_beli.KdBeli = beli.KdBeli
				WHERE obat.KdObat <> ""
				'.$where_petugas.'
				'.$where_kelompok.'
				'.$where_obat.'
				ORDER BY KdObat';
		$query = $this->db->query($sql);
		if ($query->num_rows() > 0) {
			$result = $query->result_array();
			$query->free_result();
		} else {
			$result = array();
		}
		return $result;
	}
}
